﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlServerCe;
using System.Data;

using Modelo_ETdA.Negocio.AmbienteComercial;

namespace Modelo_ETdA.Dados
{
    public class DadosAmbienteComercial
    {

        public DadosAmbienteComercial() { }

        public List<CategoriaAmbComercial> getListCategorias()
        {
            List<CategoriaAmbComercial> listaCategorias = new List<CategoriaAmbComercial>();
            SqlCeDataReader reader = null;
            try
            {
                SqlCeConnection conn = AdminBD.getConCe();
                SqlCeCommand cmd = new SqlCeCommand("select cod,nome from CategoriasAmbComercial", conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    listaCategorias.Add(new CategoriaAmbComercial(reader.GetInt32(0), reader.GetString(1)));
                }
                reader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
            }
            return listaCategorias;
        }

        public int editaAmbComercial(AmbienteComercial registo)
        {
            SqlCeCommand comando;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                //alterar nome, morada email
                string cmdstr = "update AmbienteComercial set nome='" + registo.Cabeçalho.Nome + "' , morada='" + registo.Cabeçalho.Morada + "' , email='" + registo.Cabeçalho.Email + "' , ultima_edicao=GETDATE() where cod=" + registo.Cabeçalho.Cod;
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();

                //adicionar analistas
                //inserir analistas do ambiente comercial
                cmdstr = "";
                foreach (int anal in registo.listCodAnalistas)
                {
                    cmdstr = "insert into AnalistaAmbComercial (codAnalista,codAmbC) values";
                    cmdstr += "(" + anal + "," + registo.Cabeçalho.Cod + ")";
                    comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                    comando.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                return 2;
            }
            return 0;
        }

        public int registaAmbComercial(AmbienteComercial registo)
        {
            SqlCeCommand comando;
            SqlCeDataReader reader = null;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                //inserir ambiente comercial
                int orient;
                if (registo.orientadoZona)
                    orient = 1;
                else orient = 0;
                string cmdstr = "insert into AmbienteComercial (nome,morada,email,orientadaZona,data_registo,ultima_edicao,codCategoria) values ('" + registo.Cabeçalho.Nome + "','" + registo.Cabeçalho.Morada + "','" + registo.Cabeçalho.Email + "'," + orient + ", GETDATE(),GETDATE()," + registo.Categoria.cod + ")";

                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();

                //ver codigoAmbComercial
                int codAmbComercial = 0;
                cmdstr = "select cod from AmbienteComercial where nome = '" + registo.Cabeçalho.Nome + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                reader = comando.ExecuteReader();
                if (reader.Read())
                {
                    codAmbComercial = (int)reader["cod"];
                }
                reader.Close();

                //inserir analistas do ambiente comercial
                cmdstr = "";
                foreach (int anal in registo.listCodAnalistas)
                {
                    cmdstr = "insert into AnalistaAmbComercial (codAnalista,codAmbC) values";
                    cmdstr += "(" + anal + "," + codAmbComercial + ")";
                    comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                    comando.ExecuteNonQuery();
                }
                //inserir zonas/tarefas do ambiente comercial
                cmdstr = "";
                foreach (Divisao d in registo.listDivisao)
                {
                    cmdstr = "insert into Divisao (codAmbC,codRelativo,descricao) values";
                    cmdstr += "(" + codAmbComercial + "," + d.CodRelativo + ",'" + d.Descricao + "')";
                    comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                    comando.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
                return 2;
            }
            return 0;
        }

        public AmbienteComercial consultaAmbComercial(int codAmb)
        {
            AmbienteComercial ambC = null;
            SqlCeDataReader reader = null;
            try
            {
                SqlCeConnection conn = AdminBD.getConCe();
                string strcmd = "select orientadaZona,codCategoria from AmbienteComercial where cod=" + codAmb;
                SqlCeCommand cmd = new SqlCeCommand(strcmd, conn);
                Boolean orientadoZona = true;
                int codCategoria = 0;
                string nomeCategoria = "";
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    orientadoZona = reader.GetBoolean(0);
                    codCategoria = reader.GetInt32(1);
                }
                reader.Close();

                strcmd = "SELECT nome from CategoriasAmbComercial where cod=" + codCategoria;
                cmd = new SqlCeCommand(strcmd, conn);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    nomeCategoria = (string)reader["nome"];
                }
                reader.Close();

                CategoriaAmbComercial cat = new CategoriaAmbComercial(codCategoria, nomeCategoria);
                ambC = new AmbienteComercial(getCodAnals(codAmb), listaDivisoes(codAmb), orientadoZona, cat, getCabecalho(codAmb));
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
            }
            return ambC;
        }

        public List<Divisao> listaDivisoes(int codAmb)
        {
            List<Divisao> ldivisao = new List<Divisao>();
            SqlCeDataReader reader = null;
            try
            {
                SqlCeConnection conn = AdminBD.getConCe();
                SqlCeCommand cmd = new SqlCeCommand("select cod,codRelativo,descricao from Divisao where codAmbC=" + codAmb, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ldivisao.Add(new Divisao(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2)));
                }
                reader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
            }
            return ldivisao;
        }

        public List<int> getCodAnals(int codAmb)
        {
            List<int> codsAnals = new List<int>();
            SqlCeDataReader reader = null;
            try
            {
                SqlCeConnection conn = AdminBD.getConCe();
                SqlCeCommand cmd = new SqlCeCommand("select codAnalista from AnalistaAmbComercial where codAmbC=" + codAmb, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    codsAnals.Add(reader.GetInt32(0));
                }
                reader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
            }
            return codsAnals;
        }

        public List<String> getUsernames(int codAmb)
        {
            List<String> usernames = new List<String>();
            SqlCeDataReader reader = null;
            try
            {
                SqlCeConnection conn = AdminBD.getConCe();
                SqlCeCommand cmd = new SqlCeCommand("select analist.username from AnalistaAmbComercial amba,Analista analist where analist.cod=amba.codAnalista and amba.codAmbC=" + codAmb, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    usernames.Add(reader.GetString(0));
                }
                reader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
            }
            return usernames;
        }

        public AmbienteComercialCabecalho getCabecalho(int codAmb)
        {
            SqlCeDataReader reader = null;
            try
            {
                SqlCeConnection conn = AdminBD.getConCe();
                SqlCeCommand cmd = new SqlCeCommand("select nome,morada,email,data_registo,ultima_edicao from AmbienteComercial where cod=" + codAmb, conn);
                String nome = "", morada = "", mail = "";
                DateTime dr = new DateTime(), ue = new DateTime();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    nome = reader.GetString(0);
                    morada = reader.GetString(1);
                    mail = reader.GetString(2);
                    dr = reader.GetDateTime(3);
                    ue = reader.GetDateTime(4);
                }
                reader.Close();
                return new AmbienteComercialCabecalho(codAmb, nome, morada, mail, dr, ue);
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
            }
            return null;

        }

        public int getCodCategoria(string str)
        {
            int codCat = -1; //nao encontrou
            SqlCeDataReader dataReader = null;
            try
            {
                string cmdstr = "select cod from CategoriasAmbComercial where nome='" + str + "'";
                SqlCeCommand comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    codCat = dataReader.GetInt32(0);
                }
                dataReader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                return -2;
            }
            return codCat;
        }

        public int adicionarCategoria(CategoriaAmbComercial c_amb)
        {
            SqlCeCommand comando;
            if (getCodCategoria(c_amb.nome) == -1)
            {
                SqlCeTransaction trans = null;
                try
                {
                    trans = AdminBD.getConCe().BeginTransaction();
                    string cmdstr = "insert into CategoriasAmbComercial (nome) values ('" + c_amb.nome + "')";
                    comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                    comando.ExecuteNonQuery();
                    c_amb.cod = getCodCategoria(c_amb.nome);
                    trans.Commit();
                }
                catch (SqlCeException e)
                {
                    Console.WriteLine(e.ToString());
                    trans.Rollback();
                    return 2;
                }
            }
            return 0;
        }

        public DataTable getFormatos(int codAmb)
        {
            DataTable dt = new DataTable();
            SqlCeDataAdapter a;
            try
            {
                using (a = new SqlCeDataAdapter(
                    "select faa.nome,faa.data_registo, (select COUNT(aa.codFormAvalAnalista) from FormatoAvaliacaoAnalista faa,AvaliacaoAnalista aa where aa.codFormAvalAnalista=faa.cod) as NumUtilizacoes from FormatoAvaliacaoAnalista faa,AvaliacaoAnalista aa where faa.cod=aa.codFormAvalAnalista and aa.codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
                using (a = new SqlCeDataAdapter(
                    "select faa.nome,faa.data_registo, (select COUNT(aa.codFormAvalCliente) from FormatoAvaliacaoCliente faa,AvaliacaoCliente aa where aa.codFormAvalCliente=faa.cod) as NumUtilizacoes from FormatoAvaliacaoAnalista faa,AvaliacaoAnalista aa where faa.cod=aa.codFormAvalAnalista and aa.codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }

                using (a = new SqlCeDataAdapter(
                    "select faa.nome,faa.data_registo, (select COUNT(aa.codFormAvalProfissional) from FormatoAvaliacaoProfissional faa,AvaliacaoProfissional aa where aa.codFormAvalProfissional=faa.cod) as NumUtilizacoes from FormatoAvaliacaoProfissional faa,AvaliacaoProfissional aa where faa.cod=aa.codFormAvalProfissional and aa.codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
            }
            return dt;
        }

    }
}
